﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;

namespace HotelManagement.Class
{
    public class BookingAndRoomAvailabilityDBManager
    {
        public static int InsertBookingInfo(BookingInfo b)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into Booking(ArrivalDate, ArrivalTime, DepartDate, DepartTime, NumOfNights, NumOfAdults, NumOfChildren, RoomNum, PaymentMode, Remarks, Area, BedType, PreferredRoomLoc, GuestID) values (@arrivalDate, @arrivalTime, @departDate, @departTime, @numOfNights, @numOfAdults, @numOfChildren, @roomNum, @paymentMode, @remarks, @area, @bedType, @preferredRoomLoc, @guestID)";
                comm.Parameters.AddWithValue("@arrivalDate", b.ArrivalDate);
                comm.Parameters.AddWithValue("@arrivalTime", b.ArrivalTime);
                comm.Parameters.AddWithValue("@departDate", b.DepartDate);
                comm.Parameters.AddWithValue("@departTime", b.DepartTime);
                comm.Parameters.AddWithValue("@numOfNights", b.NumOfNight);
                comm.Parameters.AddWithValue("@numOfAdults", b.NumOfAdult);
                comm.Parameters.AddWithValue("@numOfChildren", b.NumOfChildren);
                comm.Parameters.AddWithValue("@roomNum", b.RoomNum);
                comm.Parameters.AddWithValue("@paymentMode", b.PaymentMode);
                comm.Parameters.AddWithValue("@remarks", b.Remarks);
                comm.Parameters.AddWithValue("@area", b.Area);
                comm.Parameters.AddWithValue("@bedType", b.BedType);
                comm.Parameters.AddWithValue("@preferredRoomLoc", b.PreferredRoomLoc);
                comm.Parameters.AddWithValue("@guestID", b.GuestID);

                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertCreditCardInfo(CreditCard c)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into CreditCard(CardNum, CardHolderName, Type, ExpiryDate, GuestID) values (@cardNum, @cardHolderName, @type, @expiryDate, @guestId)";
                comm.Parameters.AddWithValue("@cardNum", c.CardNum);
                comm.Parameters.AddWithValue("@cardHolderName", c.CardHolderName);
                comm.Parameters.AddWithValue("@type", c.Type);
                comm.Parameters.AddWithValue("@expiryDate", c.ExpiryDate);
                comm.Parameters.AddWithValue("@guestId", c.GuestID);


                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static int InsertGuestInfo(Guest g)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into Guest(GuestID, Title, FirstName, LastName, Gender, Email, MobileNum, HomeNum, Address, City, State, ZIPCode, Country, DOB) values (@guestId, @title, @firstName, @lastName, @gender, @email, @mobileNum, @homeNum, @address, @city, @state, @zipCode, @country, @dob)";
                comm.Parameters.AddWithValue("@guestId", g.GuestID);
                comm.Parameters.AddWithValue("@title", g.Title);
                comm.Parameters.AddWithValue("@firstName", g.FirstName);
                comm.Parameters.AddWithValue("@lastName", g.LastName);
                comm.Parameters.AddWithValue("@gender", g.Gender);
                comm.Parameters.AddWithValue("@email", g.Email);
                comm.Parameters.AddWithValue("@mobileNum", g.MobileNum);
                comm.Parameters.AddWithValue("@homeNum", g.HomeNum);
                comm.Parameters.AddWithValue("@address", g.Address);
                comm.Parameters.AddWithValue("@city", g.City);
                comm.Parameters.AddWithValue("@state", g.State);
                comm.Parameters.AddWithValue("@zipCode", g.ZipCode);
                comm.Parameters.AddWithValue("@country", g.Country);
                comm.Parameters.AddWithValue("@dob", g.DOB);
                


                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }

        public static ArrayList GetAllBookingID()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT BookingID FROM Booking";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int n = (int)dr["BookingID"];
                    result.Add(n);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetAllGuestID()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT GuestID FROM Guest";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    int n = (int)dr["GuestID"];
                    result.Add(n);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static ArrayList GetAllRoomID()
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomNum FROM Room";

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string n = (string)dr["RoomNum"];
                    result.Add(n);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }



        public static BookingInfo GetBookingInfo(int id)
        {
            BookingInfo b = new BookingInfo();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT b.*, g.* FROM Booking b, Guest g WHERE b.GuestID=g.GuestID and BookingID=@bookingID";
                comm.Parameters.AddWithValue("@bookingID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    b.BookingID = (int)dr["BookingID"];
                    b.ArrivalDate = (DateTime)dr["ArrivalDate"];
                    b.ArrivalTime = (string)dr["ArrivalTime"];
                    b.DepartDate = (DateTime)dr["DepartDate"];
                    b.DepartTime = (string)dr["DepartTime"];
                    b.NumOfNight = (int)dr["NumOfNights"];
                    b.NumOfAdult = (int)dr["NumOfAdults"];
                    b.NumOfChildren = (int)dr["NumOfChildren"];
                    b.RoomNum = (string)dr["RoomNum"];
                    b.PaymentMode = (string)dr["PaymentMode"];
                    b.Remarks = (string)dr["Remarks"];
                    b.Area = (string)dr["Area"];
                    b.BedType = (string)dr["BedType"];
                    b.PreferredRoomLoc = (string)dr["PreferredRoomLoc"];
                    b.GuestID = (int)dr["GuestID"];

                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return b;
        }

        public static ArrayList GetAllVacantRoomID(string s)
        {
            ArrayList result = new ArrayList();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT RoomNum FROM Room where Status=@status";
                comm.Parameters.AddWithValue("@status", s);

                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    string n = (string)dr["RoomNum"];
                    result.Add(n);

                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return result;
        }

        public static CreditCard GetCreditCardInfoByGuestID(int id)
        {
            CreditCard c = new CreditCard();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM CreditCard WHERE GuestID=@guestID";
                comm.Parameters.AddWithValue("@guestID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    c.CardNum = (string)dr["CardNum"];
                    c.CardHolderName = (string)dr["CardHolderName"];
                    c.Type = (string)dr["Type"];
                    c.ExpiryDate = (DateTime)dr["ExpiryDate"];
                    c.GuestID = (int)dr["GuestID"];

                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return c;
        }

        public static Invoice GetInvoiceInfoByBookingId(int id)
        {
            Invoice i = new Invoice();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT InvoiceID FROM Invoice WHERE BookingID=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    i.InvoiceID = (int)dr["InvoiceID"];
                    // i.BookingID = (int)dr["BookingID"];
                    //i.ItemUsed = (string)dr["ItemUsed"];
                    //i.ExtraCharge = (double)dr["ExtraCharges"];
                    //i.Comment = (string)dr["Comments"];
                    //i.CardNum = (string)dr["CardNum"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return i;
        }

        public static Guest GetGuestInfoByBookingId(int id)
        {
            Guest g = new Guest();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT g.*, b.* FROM Booking b, Guest g WHERE g.GuestID=b.GuestID and BookingID=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    g.GuestID = (int)dr["GuestID"];
                    g.Title = (string)dr["Title"];
                    g.FirstName = (string)dr["FirstName"];
                    g.LastName = (string)dr["LastName"];
                    g.Gender = (string)dr["Gender"];
                    g.Email = (string)dr["Email"];
                    g.MobileNum = (int)dr["MobileNum"];
                    g.HomeNum = (int)dr["HomeNum"];
                    g.Address = (string)dr["Address"];
                    g.City = (string)dr["City"];
                    g.State = (string)dr["State"];
                    g.ZipCode = (int)dr["ZIPCode"];
                    g.Country = (string)dr["Country"];
                    g.DOB = (DateTime)dr["DOB"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return g;
        }

        public static BookingInfo GetBookingInfoByGuestId(int id)
        {
            BookingInfo b = new BookingInfo();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Booking WHERE GuestID=@guestID";
                comm.Parameters.AddWithValue("@guestID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    b.BookingID = (int)dr["BookingID"];
                    b.ArrivalDate = (DateTime)dr["ArrivalDate"];
                    b.ArrivalTime = (string)dr["ArrivalTime"];
                    b.DepartDate = (DateTime)dr["DepartDate"];
                    b.DepartTime = (string)dr["DepartTime"];
                    b.NumOfNight = (int)dr["NumOfNights"];
                    b.NumOfAdult = (int)dr["NumOfAdults"];
                    b.NumOfChildren = (int)dr["NumOfChildren"];
                    b.RoomNum = (string)dr["RoomNum"];
                    b.PaymentMode = (string)dr["PaymentMode"];
                    b.Remarks = (string)dr["Remarks"];
                    b.Area = (string)dr["Area"];
                    b.BedType = (string)dr["BedType"];
                    b.PreferredRoomLoc = (string)dr["PreferredRoomLoc"];
                    b.GuestID = (int)dr["GuestID"];

                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return b;
        }

        public static int InsertInvoice(Invoice i)
        {
            int rowsInserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into Invoice(BookingID, ItemUsed, ExtraCharges, Comments, CardNum) values (@bookingId, @itemUsed, @extraCharges, @comments, @cardNum)";
                comm.Parameters.AddWithValue("@bookingId", i.BookingID);
                comm.Parameters.AddWithValue("@itemUsed", i.ItemUsed);
                comm.Parameters.AddWithValue("@extraCharges", i.ExtraCharge);
                comm.Parameters.AddWithValue("@comments", i.Comment);
                comm.Parameters.AddWithValue("@cardNum", i.CardNum);

                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }

        public static Room GetRoomInfoByBookingId(int id)
        {
            Room r = new Room();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT r.RoomNum, r.TypeOfRoom FROM Room r, Booking b WHERE b.RoomNum=r.RoomNum AND b.BookingID=@bookingId";
                comm.Parameters.AddWithValue("@bookingId", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r.RoomNum = (string)dr["RoomNum"];
                    r.TypeOfRoom = (string)dr["TypeOfRoom"];
            
               
                                       
                    
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }

        public static Room GetRoomRateByRoomNum(string roomNum)
        {
            Room r = new Room();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Rate FROM Room WHERE RoomNum=@roomNum";
                comm.Parameters.AddWithValue("@roomNum", roomNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                      r.Rate = (double)((decimal)dr["Rate"]);
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            return r;
        }

        public static int UpdateBookingInfo(BookingInfo bUpdate, int bId)
        {
            int rowsUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Booking SET ArrivalDate=@arrivalDate, ArrivalTime=@arrivalTime, DepartDate=@departDate, DepartTime=@departTime, NumOfNights=@numOfNights, NumOfAdults=@numOfAdults, NumOfChildren=@numOfChildren, RoomNum=@roomNum, PaymentMode=@paymentMode, Remarks=@remarks, Area=@area, GuestID=@guestId where BookingID=@bookingID";
                comm.Parameters.AddWithValue("@arrivalDate", bUpdate.ArrivalDate);
                comm.Parameters.AddWithValue("@arrivalTime", bUpdate.ArrivalTime);
                comm.Parameters.AddWithValue("@departDate", bUpdate.DepartDate);
                comm.Parameters.AddWithValue("@departTime", bUpdate.DepartTime);
                comm.Parameters.AddWithValue("@numOfNights", bUpdate.NumOfNight);
                comm.Parameters.AddWithValue("@numOfAdults", bUpdate.NumOfAdult);
                comm.Parameters.AddWithValue("@numOfChildren", bUpdate.NumOfChildren);
                comm.Parameters.AddWithValue("@roomNum", bUpdate.RoomNum);
                comm.Parameters.AddWithValue("@paymentMode", bUpdate.PaymentMode);
                comm.Parameters.AddWithValue("@remarks", bUpdate.Remarks);
                comm.Parameters.AddWithValue("@area", bUpdate.Area);
                comm.Parameters.AddWithValue("@guestId", bUpdate.GuestID);
                comm.Parameters.AddWithValue("@bookingID", bId);


                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsUpdated;
        }

        public static int UpdateGuestInfo(Guest gUpdate, int gId)
        {
            int rowsUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Guest SET Title=@title, FirstName=@firstName, LastName=@lastName, Gender=@gender, Email=@email, MobileNum=@mobileNum, HomeNum=@homeNum, Address=@address, City=@city, State=@state, ZIPCode=@zipCode, Country=@country, DOB=@dob where GuestID=@guestID";
                comm.Parameters.AddWithValue("@title", gUpdate.Title);
                comm.Parameters.AddWithValue("@firstName", gUpdate.FirstName);
                comm.Parameters.AddWithValue("@lastName", gUpdate.LastName);
                comm.Parameters.AddWithValue("@gender", gUpdate.Gender);
                comm.Parameters.AddWithValue("@email", gUpdate.Email);
                comm.Parameters.AddWithValue("@mobileNum", gUpdate.MobileNum);
                comm.Parameters.AddWithValue("@homeNum", gUpdate.HomeNum);
                comm.Parameters.AddWithValue("@address", gUpdate.Address);
                comm.Parameters.AddWithValue("@city", gUpdate.City);
                comm.Parameters.AddWithValue("@state", gUpdate.State);
                comm.Parameters.AddWithValue("@zipCode", gUpdate.ZipCode);
                comm.Parameters.AddWithValue("@country", gUpdate.Country);
                comm.Parameters.AddWithValue("@dob", gUpdate.DOB);
                comm.Parameters.AddWithValue("@guestID", gId);
                

                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsUpdated;
        }

        public static int UpdateCreditCardInfo(CreditCard cUpdate, int gId)
        {
            int rowsUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE CreditCard SET CardHolderName=@cardHolderName, Type=@type, ExpiryDate=@expiryDate where GuestID=@guestID";
               
                comm.Parameters.AddWithValue("@cardHolderName", cUpdate.CardHolderName);
                comm.Parameters.AddWithValue("@type", cUpdate.Type);
                comm.Parameters.AddWithValue("@expiryDate", cUpdate.ExpiryDate);
                comm.Parameters.AddWithValue("@guestID", gId);


                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsUpdated;
        }

        public static Boolean CheckExistingGuestId(int gId)
        {
            Guest g = new Guest();
            Boolean a = false;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT GuestID from Guest where GuestID=@guestID";

                comm.Parameters.AddWithValue("@guestID", gId);


                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    g.GuestID = (int)dr["GuestID"];
                    a = true;

                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return a;

        }

        public static Boolean CheckVacantRoom(string s)
        {
            Room r = new Room();
            Boolean a = false;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Status from Room where Status=@status";

                comm.Parameters.AddWithValue("@status", s);


                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    r.Status = (string)dr["Status"];
                    a = true;

                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return a;

        }

        public static int UpdateRoomStatusByRoomId(Room rUpdate, string roomNum)
        {
            int rowsUpdated = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Room SET Status=@status where RoomNum=@roomNum";
                comm.Parameters.AddWithValue("@status", rUpdate.Status);
              
                comm.Parameters.AddWithValue("@roomNum", roomNum);


                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsUpdated;
        }

        public static int DeleteBooking(int bookingId)
        {
            int rowsdeleted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixRegiaDBConnectionString"].ConnectionString;

                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Booking where BookingID=@bookingId";

                comm.Parameters.AddWithValue("@bookingId", bookingId);
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsdeleted;
        }

      
    }
}